﻿using PM.Entity;
using PM.Helper;
using PM.IRepository;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
namespace PM.Repository
{
    public class PaiGongDal
    {
        public SqlSugarClient db;
        public PaiGongDal()
        {
            db = SqlSugarHelper.GetClient();
        }
        //出库 
        public string outKuCun(string barcode, string paigonganId, String uid,String pandianbianhao)
        {
            派工单任务 paigongBeen = null;
            if (!"".Equals(paigonganId))
            {
                paigongBeen = searchByPaiGong(paigonganId);

            }
            在制流转条码 barcodeEntity = SearchByBarCode(barcode,"");
            if (barcodeEntity == null)
            {
                //条码错误
                return "产品条码不是待出库状态";
            }
            /* 
               //屏蔽现在不需要卡产品

              if (!"".Equals(paigonganId))
              {
                  String chanpinxitongbianhao = SearchChanpinByDingdan(paigonganId);
                  if (!barcodeEntity.产品系统编号.Equals(chanpinxitongbianhao)) {
                      return "产品不匹配,禁止出库";
                  }
              }*/
            //检查仓位
            仓库仓位 posEntity = new PositionDAL().SearchByPosition(barcodeEntity.仓位, pandianbianhao);
            if (posEntity == null)
            {
                return "仓位条码不存在,或者仓位在盘点";
            }
            标准仓库参数子表 cangkuzibiao = searchCangkuBybianhao(barcodeEntity.仓库编号);
            if (barcodeEntity == null)
            {
                //仓库不存在
                return "仓位类别不存在";
            }
            //产生
            成品出入库主表 headEntity = new 成品出入库主表();
            成品出入库子表 detailEntity = new 成品出入库子表();
            //主表
            string startWith = cangkuzibiao.类别编号 + "U";
            string no = getNextSeq(startWith);
            headEntity.仓库类别编号 = cangkuzibiao.类别编号;
            headEntity.出入类别 = "出库";
            headEntity.单据编号 = no;
            headEntity.单据日期 = DateTime.Now;
            if ("".Equals(pandianbianhao))
            {
                if (!"".Equals(paigonganId))
                {
                    headEntity.单据类别 = "领用出库"; //
                }
                else
                {
                    headEntity.单据类别 = "其他出库"; //
                }
            }
            else {
                headEntity.单据类别 = "盘点出库";
            }
            headEntity.原始单据编号 = pandianbianhao;
            headEntity.仓库编号 = barcodeEntity.仓库编号;
            headEntity.仓库核算类别 = cangkuzibiao.核算类别;
            headEntity.经手人 = uid;
            headEntity.创建人 = uid;
            headEntity.创建时间 = DateTime.Now;
            headEntity.审核 = true;
            headEntity.审核人 = uid;
            headEntity.审核时间 = DateTime.Now;
            headEntity.库存更新 = true;
            if (paigongBeen != null)
            {
                headEntity.任务单号 = paigongBeen.派工单号;
            }
            else
            {
                headEntity.任务单号 = "";
            }

            //子表
            detailEntity.单据编号 = no;
            detailEntity.单据序号 = no + "01";
            detailEntity.产品系统编号 = barcodeEntity.产品系统编号;
            detailEntity.颜色 = barcodeEntity.颜色;
            detailEntity.自定义1 = barcodeEntity.自定义1;
            detailEntity.自定义2 = barcodeEntity.自定义2;
            detailEntity.自定义3 = barcodeEntity.自定义3;
            detailEntity.自定义4 = barcodeEntity.自定义4;
            detailEntity.自定义5 = barcodeEntity.自定义5;
            detailEntity.自定义6 = barcodeEntity.自定义6;
            detailEntity.自定义7 = barcodeEntity.自定义7;
            detailEntity.自定义8 = barcodeEntity.自定义8;
            detailEntity.库存自定义1 = barcodeEntity.工序系统编号;
            detailEntity.库存自定义2 = "";
            detailEntity.库存自定义3 = "";
            detailEntity.库存自定义4 = "";
            detailEntity.库存自定义5 = "";
            detailEntity.条码 = barcodeEntity.条码;
            detailEntity.仓位 = barcodeEntity.仓位;
            detailEntity.数量 = barcodeEntity.数量;
            //  detailEntity.导入序号 = barcodeEntity.流程单号;
            detailEntity.排序 = 1;
            detailEntity.审核 = true;
            detailEntity.创建人 = uid;
            detailEntity.创建时间 = DateTime.Now;

            //总规格
            string tSpk = headEntity.仓库类别编号
                    + "/" + (headEntity.仓库编号 == null ? "" : headEntity.仓库编号)
                    + "/" + (headEntity.仓库核算类别 == null ? "" : headEntity.仓库核算类别)
                    + "/" + (detailEntity.产品系统编号 == null ? "" : detailEntity.产品系统编号)
                    + "/" + (detailEntity.自定义1 == null ? "" : detailEntity.自定义1)
                    + "/" + (detailEntity.自定义2 == null ? "" : detailEntity.自定义2)
                    + "/" + (detailEntity.自定义3 == null ? "" : detailEntity.自定义3)
                    + "/" + (detailEntity.自定义4 == null ? "" : detailEntity.自定义4)
                    + "/" + (detailEntity.自定义5 == null ? "" : detailEntity.自定义5)
                    + "/" + (detailEntity.自定义6 == null ? "" : detailEntity.自定义6)
                    + "/" + (detailEntity.自定义7 == null ? "" : detailEntity.自定义7)
                    + "/" + (detailEntity.自定义8 == null ? "" : detailEntity.自定义8)
                    + "/" + (detailEntity.库存自定义1 == null ? "" : detailEntity.库存自定义1)
                    + "/" + (detailEntity.库存自定义2 == null ? "" : detailEntity.库存自定义2)
                    + "/" + (detailEntity.库存自定义3 == null ? "" : detailEntity.库存自定义3)
                    + "/" + (detailEntity.库存自定义4 == null ? "" : detailEntity.库存自定义4)
                    + "/" + (detailEntity.库存自定义5 == null ? "" : detailEntity.库存自定义5)
                    + "/" + (detailEntity.颜色 == null ? "" : detailEntity.颜色)
            ;
            detailEntity.总规格 = tSpk;

            //在制流转条码 ok
            barcodeEntity.状态 = "OUT";
            barcodeEntity.仓位 = barcodeEntity.仓位;
            barcodeEntity.仓库编号 = barcodeEntity.仓库编号;
           
            //在制流转条码记录 ok
            在制流转条码记录 recEntity = new 在制流转条码记录();
            recEntity.条码 = barcodeEntity.条码;
            if (!"".Equals(paigonganId))
            {
                recEntity.派工单号 = paigonganId;
                recEntity.流程单号 = paigongBeen.流程单号;
            }
            else
            {
                recEntity.派工单号 = "";
                recEntity.流程单号 = "";
            }
            recEntity.流转时间 = DateTime.Now;
            recEntity.发出工序 = "";
            //派工工序
            if (paigongBeen != null)
            {
                recEntity.收入工序 = paigongBeen.工序系统编号;
            }
            else
            {
                recEntity.收入工序 = "";
            }
            recEntity.数量 = barcodeEntity.数量;
            recEntity.重量 = barcodeEntity.重量;
            recEntity.单据编号 = no;
            recEntity.创建人 = uid;
            recEntity.盘点单号 = pandianbianhao;
            if (!"".Equals(pandianbianhao))
            {
                recEntity.备注 = "未盘点到此条码";
            }
            else {
                recEntity.备注 = "";
            }
            if (!inStorage(headEntity, detailEntity, barcodeEntity, recEntity, paigongBeen))
            {
                return "出库失败";
            }
            return "true";
        }

        public bool inStorage(成品出入库主表 headEntity, 成品出入库子表 detailEntity, 在制流转条码 barcodeEntity, 在制流转条码记录 recEntity, 派工单任务 paigongBeen)
        {
            try
            {

                db.Ado.BeginTran();
                //新增头
                db.Insertable(headEntity).ExecuteCommand();
                //新增子表
                db.Insertable(detailEntity).ExecuteCommand();
                //更新在制流转条码
                db.Updateable(barcodeEntity).ExecuteCommand();
                //新增在制流转条码记录
                db.Insertable(recEntity).IgnoreColumns(it => new { it.删除人, it.删除时间, it.删除,it.机台系统编号 }).ExecuteCommand();
                if (paigongBeen != null)
                {
                    paigongBeen.领用数量 = paigongBeen.领用数量 + barcodeEntity.数量;
                    //更新派工领用单数量
                    db.Updateable(paigongBeen).ExecuteCommand();
                }
                //库存更新
                //查询是否存在库存记录
                成品总库存 existsSotckEntity = db.Queryable<成品总库存>().Where(q => q.总规格 == detailEntity.总规格).First();
                if (existsSotckEntity == null)
                {
                    //不存在，新增
                    成品总库存 newStockEntity = new 成品总库存();
                    newStockEntity.仓库类别编号 = headEntity.仓库类别编号;
                    newStockEntity.仓库编号 = headEntity.仓库编号;
                    newStockEntity.仓库核算类别 = headEntity.仓库核算类别;
                    newStockEntity.月份 = DateTime.Now.ToString("yyyyMM");
                    newStockEntity.产品系统编号 = detailEntity.产品系统编号;
                    newStockEntity.颜色 = detailEntity.颜色;
                    newStockEntity.自定义1 = detailEntity.自定义1;
                    newStockEntity.自定义2 = detailEntity.自定义2;
                    newStockEntity.自定义3 = detailEntity.自定义3;
                    newStockEntity.自定义4 = detailEntity.自定义4;
                    newStockEntity.自定义5 = detailEntity.自定义5;
                    newStockEntity.自定义6 = detailEntity.自定义6;
                    newStockEntity.自定义7 = detailEntity.自定义7;
                    newStockEntity.自定义8 = detailEntity.自定义8;
                    newStockEntity.库存自定义1 = detailEntity.库存自定义1;
                    newStockEntity.库存自定义2 = detailEntity.库存自定义2;
                    newStockEntity.库存自定义3 = detailEntity.库存自定义3;
                    newStockEntity.库存自定义4 = detailEntity.库存自定义4;
                    newStockEntity.库存自定义5 = detailEntity.库存自定义5;
                    newStockEntity.期初数量 = 0;
                    newStockEntity.入库数量 = 0;
                    newStockEntity.出库数量 = barcodeEntity.数量;
                    newStockEntity.结存数量 = 0 - barcodeEntity.数量;
                    newStockEntity.总规格 = detailEntity.总规格;
                    db.Insertable(newStockEntity).ExecuteCommand();
                }
                else
                {
                    //存在，更新
                    existsSotckEntity.出库数量 = existsSotckEntity.出库数量 + barcodeEntity.数量;
                    existsSotckEntity.结存数量 = existsSotckEntity.期初数量 + existsSotckEntity.入库数量 - existsSotckEntity.出库数量;
                    db.Updateable(existsSotckEntity).ExecuteCommand();
                }
                //有流程单才会更新
                if (recEntity.派工单号 != null && !"".Equals(recEntity.派工单号))
                {
                    //更新流程单工序表
                    List<流程单工序明细> liuchengList = db.Queryable<流程单工序明细>().Where(q => q.流程单号 == paigongBeen.流程单号).ToList();
                    int paixv = 0;
                    foreach (流程单工序明细 liucheng in liuchengList)
                    {
                        if (paigongBeen.工序系统编号.Equals(liucheng.工序系统编号))
                        {
                            paixv = liucheng.排序;
                            break;
                        }
                    }
                    foreach (流程单工序明细 liucheng in liuchengList)
                    {
                        if (liucheng.排序 < paixv)
                        {
                            liucheng.半成品转入 = liucheng.半成品转入 + barcodeEntity.数量;
                            db.Updateable(liucheng).ExecuteCommand();
                        }
                        else if (liucheng.排序 == paixv)
                        {
                            liucheng.收入量 = liucheng.收入量 + barcodeEntity.数量;
                            liucheng.待生产量 = liucheng.收入量 - liucheng.合格产量 - liucheng.报废产量;
                            db.Updateable(liucheng).ExecuteCommand();
                        }
                    }
                    //更新流程单表
                    流程单表 liuchengbiao = db.Queryable<流程单表>().Where(q => q.流程单号 == paigongBeen.流程单号).First();
                    liuchengbiao.投料数 = liuchengbiao.投料数 + barcodeEntity.数量;
                    db.Updateable(liuchengbiao).ExecuteCommand();
                }
                db.Ado.CommitTran();
                return true;
            }
            catch (Exception ex)
            {
                db.Ado.RollbackTran();
                return false;
            }
        }

        public string getNextSeq(string startWith)
        {
            string ym = DateTime.Now.ToString("yyMM");
            startWith = startWith + ym;

            List<成品出入库主表> newList = db.Ado.SqlQuery<成品出入库主表>(" SELECT top 1 * FROM [成品出入库主表] where 单据编号 like @search order by 单据编号 desc", new { search = startWith + "%" }); ;
            成品出入库主表 newT = null;
            if (newList != null && newList.Count > 0)
            {
                newT = newList[0];
            }
            string sNext;
            if (newT == null)
            {
                sNext = startWith + "0001";
            }
            else
            {
                sNext = startWith + StringHelper.GetNext(newT.单据编号, 4);
            }
            return sNext;
        }
        public PaiGongdan SearchByDingdan(string dinganhao)
        {
            List<PaiGongdan> paiGongdanList = db.Ado.SqlQuery<PaiGongdan>("SELECT t1.[派工单号] as id,t1.[数量] as allCount,t1.[领用数量] as daipaigongCount,t3.[产品名称] as cpName,t3.[规格型号] as cpXinghao FROM[HongSheng].[dbo].[派工单任务] as t1,[HongSheng].[dbo].[流程单表] as t2,[HongSheng].[dbo].[产品资料] as t3 where t1.流程单号=t2.流程单号 and t2.产品系统编号=t3.产品系统编号 and t1.派工单号=@id", new { id = dinganhao });
            if (paiGongdanList != null && paiGongdanList.Count > 0)
            {
                return paiGongdanList[0];
            }
            else
            {
                return null;
            }

        }

        public String SearchChanpinByDingdan(string dinganhao)
        {
            List<String> paiGongdanList = db.Ado.SqlQuery<String>("SELECT t2.[产品系统编号]  FROM[HongSheng].[dbo].[派工单任务] as t1,[HongSheng].[dbo].[流程单表] as t2,[HongSheng].[dbo].[产品资料] as t3 where t1.流程单号=t2.流程单号 and t2.产品系统编号=t3.产品系统编号 and t1.派工单号=@id", new { id = dinganhao });
            if (paiGongdanList != null && paiGongdanList.Count > 0)
            {
                return paiGongdanList[0];
            }
            else
            {
                return null;
            }
        }
        //根据仓库编号查询仓库数据
        public 标准仓库参数子表 searchCangkuBybianhao(String changkubianhao)
        {
            List<标准仓库参数子表> entityList = db.Ado.SqlQuery<标准仓库参数子表>("SELECT * FROM 标准仓库参数子表 where 仓库编号 = @cangku", new { cangku = changkubianhao });
            if (entityList != null && entityList.Count > 0)
            {
                return entityList[0];
            }
            else
            {
                return null;
            }
        }
        public 派工单任务 searchByPaiGong(string paigongdanhao)
        {
            List<派工单任务> paiGongdanList = db.Ado.SqlQuery<派工单任务>("SELECT * FROM[HongSheng].[dbo].[派工单任务] where 派工单号 = @id", new { id = paigongdanhao });
            if (paiGongdanList != null && paiGongdanList.Count > 0)
            {
                return paiGongdanList[0];
            }
            else
            {
                return null;
            }

        }
        //查询条码是否正确
        public 在制流转条码 SearchByBarCode(string barcode,String pandianbianhao)
        {
            String sql = "";
            if ("".Equals(pandianbianhao))
            {
                sql = "SELECT* FROM 在制流转条码 where 条码 = @id and 标签类别 = '合格' and 状态 = 'IN'";
            }
            else {
                sql = "SELECT* FROM 在制流转条码 where 条码 = @id";
            }
            List<在制流转条码> entityList = db.Ado.SqlQuery<在制流转条码>(sql, new { id = barcode });
            if (entityList != null && entityList.Count > 0)
            {
                return entityList[0];
            }
            else
            {
                return null;
            }
        }

        //获取
        public BarCode searchChanpin(String barcode)
        {
            List<BarCode> entityList = db.Ado.SqlQuery<BarCode>("SELECT t1.数量 as shulian , t1.重量 as weight,t2.产品名称 as chanpinname,t2.规格型号 as xinghao,t1.条码 as tiaoma,t1.仓位 as cangwei,t1.仓库编号 as cangkubianhao,t1.状态 as zhuangtai FROM 在制流转条码 as t1,产品资料 as t2 where 条码=@tiaomaid and t1.产品系统编号=t2.产品系统编号 and 状态<>'CA'", new { tiaomaid = barcode });

            if (entityList != null && entityList.Count > 0)
            {
                return entityList[0];
            }
            else
            {
                return null;
            }
        }
        //获取条码打印记录
        public BarCode searchChanpinRecord(String barcode)
        {
            List<BarCode> entityList = db.Ado.SqlQuery<BarCode>("SELECT t1.数量 as shulian , t2.产品名称 as chanpinname,t2.规格型号 as xinghao,t1.条码 as tiaoma,t1.颜色 as color ,t1.状态 as zhuangtai,t1.标签类别 as biaoqianleibie , t1.重量 as zhongliang, t1.创建时间 as chuangjianshijian, t1.单重 as danzhong, t1.流程单号 as liuchengdanhao, t3.工序名称 as gongxvmingcheng FROM[HongSheng].[dbo].在制流转条码 as t1,[HongSheng].[dbo].产品资料 as t2,[HongSheng].[dbo].[工序资料] as t3 where 条码 = @tiaomaid and t1.产品系统编号 = t2.产品系统编号  and (t1.状态 = 'NA' or t1.状态 = 'IN') and t1.工序系统编号 = t3.工序系统编号", new { tiaomaid = barcode });

            if (entityList != null && entityList.Count > 0)
            {
                return entityList[0];
            }
            else
            {
                return null;
            }
        }
        public long selectMaxTiaoma() {
            List<long> entityList = db.Ado.SqlQuery<long>("SELECT max([条码]) FROM 在制流转条码");

            if (entityList != null && entityList.Count > 0)
            {
                return entityList[0];
            }
            else
            {
                return 0;
            }
        }
        public string[] insertChaiXiang(string tiaoma, decimal weight1, int shuliang1, decimal weight2, int shuliang2, string uid, DateTime timer)
        {
            try
            {
                db.Ado.BeginTran();
                long tiaomaint = selectMaxTiaoma();
                string tiaoma1 = (tiaomaint + 1) + "";
                string tiaoma2 = (tiaomaint + 2) + "";
                在制流转条码 tiaomaBeen = db.Queryable<在制流转条码>().Where(q => q.条码 == tiaoma).First();
                在制流转条码 tiaomaBeen1 = (在制流转条码)tiaomaBeen.Clone();
                tiaomaBeen1.重量 = weight1;
                tiaomaBeen1.数量 = shuliang1;
                tiaomaBeen1.原始条码 = tiaoma;
                tiaomaBeen1.条码 = tiaoma1;
                tiaomaBeen1.创建时间 = timer;
                tiaomaBeen1.创建人 = uid;
                tiaomaBeen1.终端 = "PDA";
                在制流转条码 tiaomaBeen2 = (在制流转条码)tiaomaBeen.Clone();
                tiaomaBeen2.重量 = weight2;
                tiaomaBeen2.数量 = shuliang2;
                tiaomaBeen2.原始条码 = tiaoma;
                tiaomaBeen2.条码 = tiaoma2;
                tiaomaBeen2.创建时间 = timer;
                tiaomaBeen2.创建人 = uid;
                tiaomaBeen2.终端 = "PDA";
                tiaomaBeen.状态 = "CA";
                db.Updateable(tiaomaBeen).ExecuteCommand();
                db.Insertable(tiaomaBeen1).ExecuteCommand();
                db.Insertable(tiaomaBeen2).ExecuteCommand();
                db.Ado.CommitTran();
                return new String[] { tiaoma1, tiaoma2 };
            }
            catch (Exception ex)
            {
                db.Ado.RollbackTran();
                return null;
            }
        }
        public bool yiku(String uid, String dycangwei, String barcode, String cangwei, String cangkubianhao,String panDianBianhao)
        {
            try
            {
                db.Ado.BeginTran();
                在制流转条码 barcodeEntity = db.Queryable<在制流转条码>().Where(q => q.条码 == barcode).First();
                barcodeEntity.仓位 = cangwei;
                barcodeEntity.仓库编号 = cangkubianhao;
                db.Updateable(barcodeEntity).ExecuteCommand();
                if ("".Equals(panDianBianhao))
                {
                    //在制流转条码记录
                    在制流转条码记录 recEntity = new 在制流转条码记录();
                    recEntity.条码 = barcodeEntity.条码;
                    recEntity.流程单号 = barcodeEntity.流程单号;
                    recEntity.流转时间 = DateTime.Now;
                    recEntity.发出工序 = "";
                    recEntity.收入工序 = "";
                    recEntity.数量 = barcodeEntity.数量;
                    recEntity.重量 = barcodeEntity.重量;
                    recEntity.单据编号 = "";
                    recEntity.盘点单号 = panDianBianhao;
                    recEntity.创建人 = uid;
                    recEntity.备注 = dycangwei + "移转到" + cangwei;
                    recEntity.派工单号 = "";
                    db.Insertable(recEntity).IgnoreColumns(it => new { it.删除人, it.删除时间, it.删除, it.机台系统编号 }).ExecuteCommand();
                }
                else {
                    在制流转条码记录 recJIlu = db.Queryable<在制流转条码记录>().Where(q => q.盘点单号 == panDianBianhao && q.条码 == barcode).First();
                    if (recJIlu == null)
                    {
                        //在制流转条码记录
                        在制流转条码记录 recEntity = new 在制流转条码记录();
                        recEntity.条码 = barcodeEntity.条码;
                        recEntity.流程单号 = barcodeEntity.流程单号;
                        recEntity.流转时间 = DateTime.Now;
                        recEntity.发出工序 = "";
                        recEntity.收入工序 = "";
                        recEntity.数量 = barcodeEntity.数量;
                        recEntity.重量 = barcodeEntity.重量;
                        recEntity.单据编号 = "";
                        recEntity.盘点单号 = panDianBianhao;
                        recEntity.创建人 = uid;
                        recEntity.备注 = dycangwei + "移转到" + cangwei;
                        recEntity.派工单号 = "";
                        db.Insertable(recEntity).IgnoreColumns(it => new { it.删除人, it.删除时间, it.删除, it.机台系统编号 }).ExecuteCommand();
                    }
                    else
                    {
                        recJIlu.备注 = dycangwei + "移转到" + cangwei;
                        db.Updateable(recJIlu).ExecuteCommand();
                    }
                }
                
                db.Ado.CommitTran();
                return true;
            }
            catch (Exception ex)
            {
                db.Ado.RollbackTran();
                return false;
            }
        }
        /// <summary>
        /// 生成流转记录
        /// </summary>
        /// <param name="uid"></param>
        /// <param name="barcode"></param>
        /// <param name="panDianBianhao"></param>
        public void liuzhuanjilu(String uid, String barcode, String panDianBianhao) {
            在制流转条码 barcodeEntity = db.Queryable<在制流转条码>().Where(q => q.条码 == barcode).First();
            //在制流转条码记录
            在制流转条码记录 recEntity = new 在制流转条码记录();
            recEntity.条码 = barcodeEntity.条码;
            recEntity.流程单号 = barcodeEntity.流程单号;
            recEntity.流转时间 = DateTime.Now;
            recEntity.发出工序 = "";
            recEntity.收入工序 = "";
            recEntity.数量 = barcodeEntity.数量;
            recEntity.重量 = barcodeEntity.重量;
            recEntity.单据编号 = "";
            recEntity.盘点单号 = panDianBianhao;
            recEntity.创建人 = uid;
            recEntity.备注 = "";
            recEntity.派工单号 = "";
            db.Insertable(recEntity).IgnoreColumns(it => new { it.删除人, it.删除时间, it.删除, it.机台系统编号 }).ExecuteCommand();
        }
        /// <summary>
        /// 根据派工单查询产品信息
        /// </summary>
        /// <param name="code"></param>
        /// <returns></returns>
        public List<BarCode> searchByPaiGongdan(String code) {
            List<BarCode> entityList = db.Ado.SqlQuery<BarCode>("SELECT t1.数量 as shulian , t2.产品名称 as chanpinname,t2.规格型号 as xinghao,t1.条码 as tiaoma,仓位 as cangwei,仓库编号 as cangkubianhao FROM 在制流转条码 as t1,产品资料 as t2 where t1.产品系统编号=(SELECT t2.[产品系统编号]  FROM[HongSheng].[dbo].[派工单任务] as t1,[HongSheng].[dbo].[流程单表] as t2,[HongSheng].[dbo].[产品资料] as t3 where t1.流程单号=t2.流程单号 and t2.产品系统编号=t3.产品系统编号 and t1.派工单号=@code) and t1.产品系统编号=t2.产品系统编号  and 状态='IN'", new { code = code });
            return entityList;
        }
        /// <summary>
        /// 根据仓位查询产品信息
        /// </summary>
        /// <param name="code"></param>
        /// <returns></returns>
        public List<BarCode> searchByCangwei(String code)
        {
            List<BarCode> entityList = db.Ado.SqlQuery<BarCode>("SELECT t1.数量 as shulian , t2.产品名称 as chanpinname,t2.规格型号 as xinghao,t1.条码 as tiaoma,仓位 as cangwei,仓库编号 as cangkubianhao FROM 在制流转条码 as t1,产品资料 as t2 where 仓位=@code and t1.产品系统编号=t2.产品系统编号 and 状态='IN'", new { code = code });
            return entityList;
        }
        /// <summary>
        /// 根据区域查询产品信息
        /// </summary>
        /// <param name="code"></param>
        /// <returns></returns>
        public List<BarCode> searchByQuYu(String code)
        {
            List<BarCode> entityList = db.Ado.SqlQuery<BarCode>("SELECT t1.数量 as shulian , t2.产品名称 as chanpinname,t2.规格型号 as xinghao,t1.条码 as tiaoma,仓位 as cangwei,仓库编号 as cangkubianhao FROM 在制流转条码 as t1,产品资料 as t2 where 仓位 in (select 仓位编号 from 仓库仓位 where 区域=@code) and t1.产品系统编号=t2.产品系统编号 and 状态='IN'", new { code = code });
            return entityList;
        }
       
        /// <summary>
        /// 锁库
        /// </summary>
        /// <param name="isSuoKu"></param>
        public bool suoku(String quyu, bool isSuoKu, String pandianbianhao, String uid, bool isOk, int tiaomaCount,int allPandianCount,decimal pandianWeight) {
            
            int count = db.Ado.ExecuteCommand("update 仓库仓位 set 盘点 =@isSuoKu where 区域=@quyu", new { isSuoKu = isSuoKu, quyu = quyu });
            if (count == 0)
            {
                return false;
            }

            if (isSuoKu)
            {
                产品盘点单 pandianDan = new 产品盘点单();
                pandianDan.盘点单号 = pandianbianhao;
                pandianDan.盘点日期 = DateTime.Now;
                pandianDan.区域 = quyu;
                pandianDan.创建人 = uid;
                pandianDan.创建时间 = DateTime.Now;
                db.Insertable(pandianDan).IgnoreColumns(it => new { it.条码个数, it.盘点总数量, it.盘点总重量, it.备注,it.删除,it.删除人,it.删除时间
                    ,it.修改时间,it.修改人
                }).ExecuteCommand();
            }
            else {
                if (isOk) {
                    var t10 = db.Updateable<产品盘点单>()
               .UpdateColumns(it => new 产品盘点单() { 条码个数 = tiaomaCount, 盘点总数量 = allPandianCount, 盘点总重量 = pandianWeight,
                修改人=uid,修改时间= DateTime.Now
               })
               .Where(it => it.盘点单号 == pandianbianhao).ExecuteCommand();
                }
                else { 
                var t10 = db.Updateable<产品盘点单>()
                .UpdateColumns(it => new 产品盘点单() { 删除 = true, 删除人 = uid, 删除时间 = DateTime.Now })
                .Where(it => it.盘点单号 == pandianbianhao).ExecuteCommand();
                }
            }
            return true;
        }
        public String getpandianDan() {
          
                String yy = DateTime.Today.ToString("yy");
                String sum = db.Ado.GetString("select max(盘点单号) from 产品盘点单 where 盘点单号 like @pandiandanhao", new { pandiandanhao = yy+"%" });
                if (sum == null || "".Equals(sum))
                {
                   return   yy + "0001";
                }
                else
                {
                    int year=int.Parse(yy + "0000");
                    int oldyear= int.Parse(sum);
                    if (year > oldyear) {
                           return yy + "0001";
                }
                else{
                    return (oldyear+1)+"";
                }
                   
                }
            
        }
        /// <summary>
        /// 查询区域仓位信息
        /// </summary>
        /// <param name="quyu"></param>
        /// <returns></returns>
        public List<CangWeiReturn> searchPanDianCangWei(String quyu) {
            List<CangWeiReturn> cangkuList=db.Ado.SqlQuery<CangWeiReturn>("select 仓库仓位.仓位编号 as cangwei,仓库仓位.仓库编号 as cangkubianhao from 仓库仓位 where 仓库仓位.区域 = @quyu", new { quyu=quyu});
            return cangkuList;
        }
        /// <summary>
        /// 查询区域条码信息
        /// </summary>
        /// <param name="quyu"></param>
        /// <returns></returns>
        public List<BarCode> searchPanDianTiaoma(String quyu)
        {
            List<BarCode> cangkuList = db.Ado.SqlQuery<BarCode>("select 条码 as tiaoma,仓位 as cangwei,状态 as zhuangtai,数量 as shulian,重量 as weight from 在制流转条码 where 仓位 in (select 仓库仓位.仓位编号 from 仓库仓位 where 仓库仓位.区域=@quyu) and 状态='IN' ", new { quyu = quyu });
            return cangkuList;
        }

        //获取外发拆箱条码打印记录
        public BarCode searchXianChanChanpinRecord(String barcode)
        {
            List<BarCode> entityList = db.Ado.SqlQuery<BarCode>("SELECT t1.数量 as shulian , t2.产品名称 as chanpinname,t2.规格型号 as xinghao,t1.条码 as tiaoma,t1.颜色 as color ,t1.状态 as zhuangtai,t1.标签类别 as biaoqianleibie , t1.重量 as zhongliang, t1.创建时间 as chuangjianshijian, t1.单重 as danzhong, t1.流程单号 as liuchengdanhao, t3.工序名称 as gongxvmingcheng FROM[HongSheng].[dbo].在制流转条码 as t1,[HongSheng].[dbo].产品资料 as t2,[HongSheng].[dbo].[工序资料] as t3 where 条码 = @tiaomaid and t1.产品系统编号 = t2.产品系统编号  and (t1.状态 = 'NA' or t1.状态 = 'OUT') and t1.工序系统编号 = t3.工序系统编号", new { tiaomaid = barcode });

            if (entityList != null && entityList.Count > 0)
            {
                return entityList[0];
            }
            else
            {
                return null;
            }
        }
        /// <summary>
        /// 外发拆箱
        /// </summary>
        /// <param name="req"></param>
        /// <param name="uid"></param>
        /// <param name="timer"></param>
        /// <returns></returns>
        public WfChaiXiangReq insertWfChaiXiang(WfChaiXiangReq req, string uid, DateTime timer)
        {
            try
            {

                db.Ado.BeginTran();
                long tiaomaint = selectMaxTiaoma();
                String oldeBoderCode=req.oldbarcode;
                在制流转条码 tiaomaBeen = db.Queryable<在制流转条码>().Where(q => q.条码 == oldeBoderCode).First();
                long tiaomaCount = tiaomaint;
                for (int i = 0; i < req.wfChaXiangList.Count; i++) {
                    WfChaiXiang wfChaiXiang = req.wfChaXiangList[i];
                    if (wfChaiXiang.weight == 0) {
                        continue;
                    }
                    
                    wfChaiXiang.timer = timer.ToString();
                    tiaomaCount = tiaomaCount +1;
                    wfChaiXiang.barcode = tiaomaCount + "";
                    在制流转条码 tiaomaBeen1 = (在制流转条码)tiaomaBeen.Clone();
                    tiaomaBeen1.重量 = wfChaiXiang.weight;
                    tiaomaBeen1.数量 = wfChaiXiang.count;
                    tiaomaBeen1.原始条码 = oldeBoderCode;
                    tiaomaBeen1.条码 = wfChaiXiang.barcode;
                    tiaomaBeen1.创建时间 = timer;
                    tiaomaBeen1.创建人 = uid;
                    tiaomaBeen1.终端 = "PDA";
                    tiaomaBeen1.颜色 = wfChaiXiang.color;
                    db.Insertable(tiaomaBeen1).ExecuteCommand();
                }
                tiaomaBeen.状态 = "CA";
                db.Updateable(tiaomaBeen).ExecuteCommand();
                db.Ado.CommitTran();
                return req;
            }
            catch (Exception ex)
            {
                db.Ado.RollbackTran();
                return null;
            }
        }
        //查询在制条码是否正确
        public 在制流转条码 ZaiZhiSearchByBarCode(string barcode)
        {
            String sql= "SELECT * FROM 在制流转条码 where 条码 = @id and (状态 = 'IN' or 状态='OUT')";
          
            List<在制流转条码> entityList = db.Ado.SqlQuery<在制流转条码>(sql, new { id = barcode });
            if (entityList != null && entityList.Count > 0)
            {
                return entityList[0];
            }
            else
            {
                return null;
            }
        }
        //查询在制品仓库仓库是否正确
        public 仓库仓位 ZaiZhiSearchByPosition(string position)
        {
            Expression<Func<仓库仓位, bool>> where = (p => p.仓位编号 == position && p.盘点 == false && p.现场==true);
            仓库仓位 entity = db.Queryable<仓库仓位>().Where(where).First();
            return entity;
        }
        /// <summary>
        /// 在制品入库
        /// </summary>
        /// <param name="barcode"></param>
        /// <param name="position"></param>
        /// <param name="uid"></param>
        /// <returns></returns>
        public string ZaiZhiIn(string barcode, string position, string uid)
        {
            //检查barcode
            在制流转条码 barcodeEntity = ZaiZhiSearchByBarCode(barcode);
            if (barcodeEntity == null)
            {
                return "产品条码IN或者OUT状态";
            }
            //检查仓位
            仓库仓位 posEntity = ZaiZhiSearchByPosition(position);
            if (posEntity == null)
            {
                return "仓位条码不存在,或者仓位不是现场仓位或者仓位在盘点";
            }
            barcodeEntity.仓位 = posEntity.仓位编号;
            barcodeEntity.仓库编号 = posEntity.仓库编号;

            //在制流转条码记录
            在制流转条码记录 recEntity = new 在制流转条码记录();
            recEntity.条码 = barcodeEntity.条码;
            recEntity.流程单号 = barcodeEntity.流程单号;
            recEntity.流转时间 = DateTime.Now;
            recEntity.发出工序 = "";
            recEntity.收入工序 = "";
            recEntity.数量 = barcodeEntity.数量;
            recEntity.重量 = barcodeEntity.重量;
            recEntity.单据编号 = "";
            recEntity.创建人 = uid;
            recEntity.备注 = "转入" + posEntity.仓位编号 + "仓位";
            recEntity.盘点单号 = "";
            recEntity.派工单号 = "";
            try
            {
                db.Ado.BeginTran();
                //更新在制流转条码
                db.Updateable(barcodeEntity).ExecuteCommand();
                //新增在制流转条码记录
                db.Insertable(recEntity).IgnoreColumns(it => new { it.删除人, it.删除时间, it.删除, it.机台系统编号 }).ExecuteCommand();
                db.Ado.CommitTran();
                return "true";
            }
            catch (Exception ex)
            {
                db.Ado.RollbackTran();
                return "false";
            }
        }

        /// <summary>
        /// 在制品根据派工单查询产品信息
        /// </summary>
        /// <param name="code"></param>
        /// <returns></returns>
        public List<BarCode> searchZaiZhiByPaiGongdan(String code)
        {
            List<BarCode> entityList = db.Ado.SqlQuery<BarCode>("  SELECT t1.数量 as shulian , t2.产品名称 as chanpinname,t2.规格型号 as xinghao,t1.条码 as tiaoma,仓位 as cangwei,仓库编号 as cangkubianhao FROM 在制流转条码 as t1,产品资料 as t2 where t1.产品系统编号=(SELECT t2.[产品系统编号]  FROM[HongSheng].[dbo].[派工单任务] as t1,[HongSheng].[dbo].[流程单表] as t2,[HongSheng].[dbo].[产品资料] as t3 where t1.流程单号=t2.流程单号 and t2.产品系统编号=t3.产品系统编号 and t1.派工单号=@code) and t1.产品系统编号=t2.产品系统编号  and (状态='NA'or 状态='OUT') and t1.仓位 in (select 仓位编号 from 仓库仓位 where 仓库仓位.现场=1)", new { code = code });
            return entityList;
        }

    }
}
